package imse.team0.db.dal;

import org.hsqldb.jdbc.JDBCDataSource;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author Eugen, 4/28/12 11:54 PM
 */

/**
 * Performs basic queries against the Warehouse Inventory database
 */
public class LocalInventoryQueryService {
    private static DataSource ds;
    private final static String INVENTORYDB_LOCATION = "localhost";
    static final String DELIM = "=";

    static {
        JDBCDataSource dataSource = new JDBCDataSource();
        dataSource.setUrl("jdbc:hsqldb:hsql://" + INVENTORYDB_LOCATION + "/localinventory");
        dataSource.setUser("TEAM0");
        dataSource.setPassword("cloudproject");
        ds = dataSource;
    }



    private static class StatementRepository {
        private static final String selectOneItem = "SELECT * FROM cloudproject.local_inventory WHERE id = ?";
        private static final String decreaseQuantity = "UPDATE cloudproject.local_inventory SET qty = qty - ? where id = ?";
        private static final String increaseQuantity = "UPDATE cloudproject.local_inventory SET qty = qty + ? where id = ?";
        private static final String getItemVolume = "SELECT volume FROM cloudproject.local_inventory WHERE id = ?";
    }

    public int retrieveProductVolume(String id) throws SQLException {
            Connection conn = null;
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.getItemVolume);
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();
            int volm = 0 ;
            while ( rs.next() ) {
                 volm = rs.getInt("volume");
                 break;
            }

            return volm;
    }

    /**
     * Removes a specified quantity of items from inventory. Actual removal happens only when
     * all items could be removed from inventory, otherwise no item is removed.
     * <p/>
     * Returns how many items couldn't be removed from inventory:
     * 0 - on success
     * requested - available - on failure
     *
     * @param id
     * @param orderedQuantity
     * @return
     * @throws java.sql.SQLException
     */
    public int removeItem(String id, int orderedQuantity) throws SQLException {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.decreaseQuantity);
            stmt.setInt(1, orderedQuantity);
            stmt.setString(2, id);
            int count = stmt.executeUpdate();
        } catch (SQLIntegrityConstraintViolationException ex) {
            // assuming update failed because of CHECK constraint violation
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.selectOneItem);
            stmt.setString(1, id);
            ResultSet rs = stmt.executeQuery();
            rs.next();
            int quantity = rs.getInt("qty");
            rs.close();
            return quantity;
        } finally {
            if (conn != null)
                conn.close();
        }
        return orderedQuantity;
    }

    /**
     * Puts the back a given quantity for some item id (e.g. during order cancellation events)
     * @param id
     * @param orderedQuantity
     * @throws java.sql.SQLException
     */
    public void addItem(String id, int orderedQuantity) throws SQLException {
        Connection conn = null;
        try {
            conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(StatementRepository.increaseQuantity);
            stmt.setInt(1, orderedQuantity);
            stmt.setString(2, id);
            int count = stmt.executeUpdate();
        } finally {
            if (conn != null)
                conn.close();
        }
        return;
    }

}
